﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using DVSProject.Models;

namespace DVSProject.Lib
{
    public class DepartmentRepo
    {
        public const string m_msg_head = "Department";

        public IEnumerable<DepartmentModels> GetDepartment()
        {
            List<DepartmentModels> Departments = new List<DepartmentModels>();
            string SQLCommand = @"select * from TBDepartment WHERE Status <> 'Delete'";
            using (var db = new DBHelper())
            {
                try
                {
                    var reader = db.ExecuteReader(SQLCommand);
                    while (reader.Read())
                    {
                        DepartmentModels Department = new DepartmentModels();
                        Department.dept_id = (int)reader["dept_id"];
                        Department.dept_name_th = (string)reader["dept_name_th"];
                        Department.dept_name_en = (string)reader["dept_name_en"];
                        Department.Status = (string)reader["Status"];

                        Departments.Add(Department);
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0001", ex));
                }
            }
            return Departments.ToList();
        }

        public DepartmentModels GetDepartmentById(int id)
        {
            DepartmentModels Department = new DepartmentModels();
            string SQLCommand = @"select * from TBDepartment where dept_id=@dept_id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.AddParameter("@dept_id", id);
                    var reader = db.ExecuteReader(SQLCommand);
                    if (reader.Read())
                    {
                        Department.dept_id = (int)reader["dept_id"];
                        Department.dept_name_th = (string)reader["dept_name_th"];
                        Department.dept_name_en = (string)reader["dept_name_en"];
                        Department.Status = (string)reader["Status"];
                       
                    }
                }
                catch (Exception ex)
                {
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0002", ex));
                }
            }
            return Department;
        }

        public bool Create(DepartmentModels Department)
        {
            bool result = true;
            string SQLCommand = @"INSERT into TBDepartment
                                    values (@dept_name_th,@FeederRemarkEng,@Status)";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@dept_name_th", Department.dept_name_th);
                    db.AddParameter("@dept_name_en", Department.dept_name_en);
                    db.AddParameter("@Status", "Active");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0003", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Edit(DepartmentModels Department)
        {
            bool result = true;
            string SQLCommand = @"Update TBDepartment 
                                    Set dept_name_th=@dept_name_th,
                                        dept_name_en=@dept_name_en
                                    where dept_id=@dept_id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@dept_id", Department.dept_id);
                    db.AddParameter("@dept_name_th", Department.dept_name_th);
                    db.AddParameter("@dept_name_en", Department.dept_name_en);

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0004", ex));
                    return false;
                }
            }
            return result;
        }

        public bool Delete(DepartmentModels Department)
        {
            bool result = true;
            string SQLCommand = @"Update TBDepartment
                                    Set Status=@Status
                                    where dept_id=@dept_id";

            using (var db = new DBHelper())
            {
                try
                {
                    db.BeginTransaction();
                    db.AddParameter("@dept_id", Department.dept_id);
                    db.AddParameter("@Status", "Delete");

                    db.ExecuteNonQuery(SQLCommand, DbConnectionState.KeepOpen);
                    db.CommitTransaction();
                }
                catch (Exception ex)
                {
                    db.RollbackTransaction();
                    TraceLog.WriteError(string.Format("{0}_{1}->{2}", m_msg_head, "0005", ex));
                    return false;
                }
            }
            return result;
        }
    }
}